﻿Public Class clientes

    Dim cadena As String = "Data Source=CESAR-PC\SQLSERVER;Initial Catalog=CAPCrepuestos;Integrated Security=True"
#Region "Variables"
    Enum termino
        aprobado
        rechazado
    End Enum
    Dim accion As estado = estado.insertar

    Enum estado
        insertar
        modificar
    End Enum
#End Region
#Region "Funciones genericas"
    Private Sub cargarCombo(ByVal combo As ComboBox, ByRef datos As DataTable, ByVal pk As String, ByVal descripcion As String)

        combo.DataSource = datos
        combo.DisplayMember = descripcion
        combo.ValueMember = pk

    End Sub

    Private Function leo_tabla(ByVal nombreTabla As String) As Data.DataTable

        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion

        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM " & nombreTabla
        tabla.Load(cmd.ExecuteReader())

        conexion.Close()

        Return tabla

    End Function
#End Region
#Region "Load"
    Private Sub clientes_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        txtBarrio.Enabled = False
        txtCUIT.Enabled = False
        txtDireccion.Enabled = False
        txtFechaAlta.Enabled = False
        txtLocalidad.Enabled = False
        txtNroCliente.Enabled = False
        txtRazonSocial.Enabled = False
        txtTelefono.Enabled = False
        txtTransporte.Enabled = False
        txtContactoCelular.Enabled = False
        txtContactoApellido.Enabled = False
        txtContactoNombre.Enabled = False

    End Sub


    Private Sub clientes_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If (MsgBox("¿Desea salir?", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Atencion") = MsgBoxResult.Yes) Then
            e.Cancel = False
        Else
            e.Cancel = True


        End If
    End Sub
#End Region
#Region "Botones"
    Private Sub btnNuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNuevo.Click
        cmbNombreCliente.Text = ""
        txtContactoApellido.Text = ""
        txtContactoCelular.Text = ""
        txtCUIT.Text = ""
        txtBarrio.Text = ""
        txtDireccion.Text = ""
        txtNroCliente.Text = ""
        txtLocalidad.Text = ""
        txtTelefono.Text = ""
        txtTransporte.Text = ""
        txtRazonSocial.Text = ""

        txtRazonSocial.Enabled = True
        txtBarrio.Enabled = True
        txtLocalidad.Enabled = True
        txtCUIT.Enabled = True
        txtDireccion.Enabled = True
        txtFechaAlta.Enabled = True
        txtTelefono.Enabled = True
        txtTransporte.Enabled = True
        txtContactoApellido.Enabled = True
        txtContactoNombre.Enabled = True
        txtContactoCelular.Enabled = True

        txtFechaAlta.Text = Today
        txtNroCliente.Text = cargarMayor()
        btnModificar.Enabled = False
        Me.accion = estado.insertar


    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click, Button13.Click

        filtroBuscar.Show()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGuardar.Click
        If Me.accion = estado.insertar Then
            insertar()
        Else
            modificar()
        End If

        btnNuevo.Enabled = True
        btnModificar.Enabled = True

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModificar.Click

        cmbNombreCliente.Enabled = False
        txtRazonSocial.Enabled = True
        txtCUIT.Enabled = True
        txtDireccion.Enabled = True
        txtBarrio.Enabled = True
        txtLocalidad.Enabled = True
        txtTelefono.Enabled = True
        txtTransporte.Enabled = True
        btnNuevo.Enabled = False
        Me.accion = estado.modificar
    End Sub

    Private Sub btnEliminar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEliminar.Click
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "DELETE FROM clientes WHERE numero = " & txtNroCliente.Text
        cmd.ExecuteNonQuery()
        conexion.Close()

    End Sub

    Private Sub btnCancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelar.Click
        Me.resetData()
        btnNuevo.Enabled = True
        btnModificar.Enabled = True
    End Sub

    Private Sub btnSalir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSalir.Click
        Me.Close()

    End Sub
#End Region
#Region "Funciones"
    Private Function insertar() As termino
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim consulta As String = ""

        conexion.ConnectionString = cadena
        conexion.Open()

        consulta = "insert into clientes (nombre, id, fecha_alta, razon_social,CUIT,direccion,barrio,localidad,telefono,transporte,contacto_nombre,contacto_apellido,celular)"
        consulta &= "values ('" & cmbNombreCliente.Text & "'"
        consulta &= ", '" & Me.txtNroCliente.Text & "'"
        consulta &= ", '" & Me.txtFechaAlta.Text & "'"
        consulta &= ", '" & Me.txtRazonSocial.Text & "'"
        consulta &= ", '" & Me.txtCUIT.Text & "'"
        consulta &= ", '" & Me.txtDireccion.Text & "'"
        consulta &= ", '" & Me.txtBarrio.Text & "'"
        consulta &= ", '" & Me.txtLocalidad.Text & "'"
        consulta &= ", '" & Me.txtTelefono.Text & "'"
        consulta &= ", '" & Me.txtTransporte.Text & "'"
        consulta &= ", '" & Me.txtContactoNombre.Text & "'"
        consulta &= ", '" & Me.txtContactoApellido.Text & "'"
        consulta &= ", '" & Me.txtCelular.Text & "')"


        cmd.CommandType = CommandType.Text
        cmd.CommandText = consulta
        cmd.Connection = conexion
        cmd.ExecuteNonQuery()
        conexion.Close()

        MsgBox("El cliente fue guardado exitosamente", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "ATENCION")
        Me.resetData()

        Return termino.aprobado
    End Function


    Private Function modificar() As termino
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim modificacion As String = ""

        conexion.ConnectionString = cadena
        conexion.Open()

        modificacion = "UPDATE clientes"
        modificacion &= " Set razon_social='" & Me.txtRazonSocial.Text & "'"
        modificacion &= ", CUIT='" & Me.txtCUIT.Text & "'"
        modificacion &= ", direccion='" & Me.txtDireccion.Text & "'"
        modificacion &= ", barrio='" & Me.txtBarrio.Text & "'"
        modificacion &= ", localidad='" & Me.txtLocalidad.Text & "'"
        modificacion &= ", telefono='" & Me.txtTelefono.Text & "'"
        modificacion &= ", transporte='" & Me.txtTransporte.Text & "'"
        modificacion &= " WHERE (nombre='" & Me.cmbNombreCliente.Text & "')"

        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = modificacion

        cmd.ExecuteNonQuery()
        conexion.Close()

        Return termino.aprobado

    End Function

    Private Sub resetData()
        cmbNombreCliente.Text = ""
        txtContactoNombre.Text = ""
        txtContactoApellido.Text = ""
        txtContactoCelular.Text = ""
        txtCUIT.Text = ""
        txtBarrio.Text = ""
        txtDireccion.Text = ""
        txtNroCliente.Text = ""
        txtLocalidad.Text = ""
        txtTelefono.Text = ""
        txtTransporte.Text = ""
        txtNroCliente.Text = ""
        txtRazonSocial.Text = ""

        txtBarrio.Enabled = False
        txtCUIT.Enabled = False
        txtDireccion.Enabled = False
        txtFechaAlta.Enabled = False
        txtLocalidad.Enabled = False
        txtNroCliente.Enabled = False
        txtRazonSocial.Enabled = False
        txtTelefono.Enabled = False
        txtTransporte.Enabled = False
        txtContactoCelular.Enabled = False
        txtContactoApellido.Enabled = False
        txtContactoNombre.Enabled = False


    End Sub

    Private Function cargarMayor() As Integer
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim max As Integer

        conexion.ConnectionString = cadena
        conexion.Open()

        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT MAX(id) as Legajo FROM clientes"
        max = cmd.ExecuteScalar
        conexion.Close()

        Return max + 1
    End Function
#End Region
#Region "Validaciones"

    Private Sub txtDireccion_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtDireccion.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
    End Sub

    Private Sub txtNroCliente_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNroCliente.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack Then e.Handled = True
        txtNroCliente.MaxLength = 6
    End Sub

    Private Sub cmbNombreCliente_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmbNombreCliente.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        cmbNombreCliente.MaxLength = 25
    End Sub

    Private Sub txtRazonSocial_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtRazonSocial.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        txtRazonSocial.MaxLength = 30
    End Sub

    Private Sub txtCUIT_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCUIT.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack Then e.Handled = True
    End Sub


    Private Sub txtBarrio_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBarrio.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        txtBarrio.MaxLength = 30
    End Sub

    Private Sub txtLocalidad_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtLocalidad.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True

    End Sub

    Private Sub txtTelefono_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTelefono.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 And Asc(e.KeyChar) <> 45 Then e.Handled = True
        txtTelefono.MaxLength = 18
    End Sub

    Private Sub txtTransporte_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTransporte.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        txtTransporte.MaxLength = 30
    End Sub

    Private Sub txtContactoNombre_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtContactoNombre.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        cmbNombreCliente.MaxLength = 25
    End Sub

    Private Sub txtContactoApellido_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtContactoApellido.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        txtApellidoContacto.MaxLength = 25
    End Sub

    Private Sub txtContactoCelular_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtContactoCelular.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 And Asc(e.KeyChar) <> 45 And Asc(e.KeyChar) <> 43 Then e.Handled = True
        txtContactoCelular.MaxLength = 30
    End Sub
#End Region

   
End Class
